﻿using System;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using SharePointUtilitiesForDevelopers.Action;
using SharePointUtilitiesForDevelopers.Actions.Forms;
using SharePointUtilitiesForDevelopers.Repository;

namespace SharePointUtilitiesForDevelopers.Actions
{
    /// <summary>
    /// Generates sql select statements for a list.
    /// </summary>
    public class GenerateListSqlAction: IAction
    {
        private readonly ISharePointListRepository _listRepository;

        /// <summary>
        /// Gets the title.
        /// </summary>
        /// <value>The title.</value>
        public string Title
        {
            get { return Properties.Resources.GenerateListSqlActionTitle; }
        }

        /// <summary>
        /// Gets the description.
        /// </summary>
        /// <value>The description.</value>
        public string Description
        {
            get { return Properties.Resources.GenerateListSqlActionDescription; }
        }

        /// <summary>
        /// Gets the target.
        /// </summary>
        /// <value>The target.</value>
        public ActionTarget Target
        {
            get { return ActionTarget.List; }
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="GenerateListSqlAction"/> class.
        /// </summary>
        /// <param name="listRepository">The list repository.</param>
        public GenerateListSqlAction(ISharePointListRepository listRepository)
        {
            _listRepository = listRepository;
        }

        /// <summary>
        /// Executes the action.
        /// </summary>
        /// <param name="actionContext">The context.</param>
        public void Execute(ActionContext actionContext)
        {
            if(actionContext.Entity is SharePointList)
            {
                var list = (SharePointList)actionContext.Entity;

                // Get list details
                if (list.Fields == null)
                {
                    _listRepository.LoadListFields(list, actionContext.Web.Url);
                }

                if (list.Fields != null)
                {
                    // Show form
                    var form = ServiceLocator.Resolve<SqlForm>();

                    form.Sql = GenerateSelectStatement(list, actionContext.Web.SiteUserInfoList);
                    form.ShowDialog(actionContext.Owner as IWin32Window);
                }
            }
        }
        
        /// <summary>
        /// Generates the select statement.
        /// </summary>
        /// <returns></returns>
        private static string GenerateSelectStatement(SharePointList list, SharePointList siteUserInfoList)
        {
            var firstCol = true;

            // Create sql
            var sql = new StringBuilder();
            sql.AppendLine("SELECT ");

            // Add each list column
            foreach(var field in list.Fields.Where(f => !f.Hidden && !string.IsNullOrEmpty(f.DatabaseColumnName)))
            {
                switch (field.FieldType)
                {
                    case SharePointFieldType.User:
                        sql.Append(
                            string.Format(
                                "\t{0}(SELECT {1} FROM AllUserData WHERE tp_iscurrentversion=1 AND tp_RowOrdinal=0 AND tp_CalculatedVersion=0 AND tp_DeleteTransactionId=0x AND tp_listid='{2}' AND tp_id=list.{3}) AS \"{4}\"{5}",
                                firstCol ? string.Empty : ",",
                                list.Fields.GetColNameFromShowField(field.ShowField), // The show field is the internal name, look it up
                                siteUserInfoList.Id, // User the site user info list
                                field.DatabaseColumnName,
                                field.DisplayName,
                                Environment.NewLine));

                        break;

                    case SharePointFieldType.UserMulti:
                        sql.Append(
                            string.Format(
                                "\t{0}(SELECT '{1}') AS \"{2}\"{3}",
                                firstCol ? string.Empty : ",",
                                "UserMulti not implemented",
                                field.DisplayName,
                                Environment.NewLine));
                        break;

                    case SharePointFieldType.Lookup:
                        sql.Append(
                            string.Format(
                                "\t{0}(SELECT {1} FROM AllUserData WHERE tp_iscurrentversion=1 AND tp_RowOrdinal=0 AND tp_CalculatedVersion=0 AND tp_DeleteTransactionId=0x AND tp_listid='{2}' AND tp_id=list.{3}) AS \"{4}\"{5}",
                                firstCol ? string.Empty : ",",
                                list.Fields.GetColNameFromShowField(field.ShowField), // The show field is the internal name, look it up
                                field.List,
                                field.DatabaseColumnName,
                                field.DisplayName,
                                Environment.NewLine));

                        break;

                    case SharePointFieldType.LookupMulti:
                        sql.Append(
                            string.Format(
                                "\t{0}(SELECT '{1}') AS \"{2}\"{3}",
                                firstCol ? string.Empty : ",",
                                "LookupMulti not implemented",
                                field.DisplayName,
                                Environment.NewLine));
                        break;

                    default:
                        sql.Append(
                            string.Format(
                                "\t{0}list.{1} as \"{2}\"{3}",
                                firstCol ? string.Empty : ",",
                                field.DatabaseColumnName,
                                field.DisplayName,
                                Environment.NewLine));

                        break;
                }

                firstCol = false;
            }

            sql.AppendLine("FROM");
            sql.AppendLine("\tAllUserData AS list");
            sql.AppendLine("WHERE");
            sql.Append("\tlist.tp_iscurrentversion=1 AND list.tp_RowOrdinal=0 AND list.tp_CalculatedVersion=0 AND list.tp_DeleteTransactionId=0x");
            sql.AppendFormat(" AND list.tp_listid='{0}' {1}", list.Id, Environment.NewLine);

            return sql.ToString();

        }
    }
}